﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql;
using VIB.Model;
using MySql.Data.MySqlClient;
using System.Data;
using System.Windows;

namespace VIB
{
    public static class DataManager
    {
        private static string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["VIBE.Properties.Settings.vibeConnectionString"].ConnectionString;
        private static MySqlConnection connection = new MySqlConnection(connectionString);

        public static void AddOwnerShip(int idUser, string isbn, DateTime borrowedUntil)
        {
            // does the book exist and is availiable?
            int idBook = GetIdBook(isbn);

            if (idBook > 0)
            {
                //create sql command
                string query = "INSERT INTO vibe.ownership (iduser, idbook, date_created, date_borrowed_until)" +
                                "VALUES (@iduser,@idbook, (SELECT NOW()), @date_borrowed_until)";

                MySqlCommand cmd = new MySqlCommand(query, connection);

                cmd.Parameters.AddWithValue("@iduser", idUser);
                cmd.Parameters.AddWithValue("@idbook", idBook);
                cmd.Parameters.AddWithValue("@date_borrowed_until", borrowedUntil);

                cmd.ExecuteNonQuery();
            }
            else
            {
                throw new Exception("BookNotAvailiable");
            }
            
        }

        public static void ReturnBook(int idUser, string isbn)
        {           
            int idBook = GetIdBook(isbn, idUser);

            if (idBook > 0)
            {
                int idOwnership = GetIdOwnership(idUser, idBook);

                if (idOwnership > 0)
                {
                    CancelOwnerShip(idOwnership);
                }
                else
                {
                    throw new Exception("BookNotBorrowed");
                }
            }
            else
            {
                throw new Exception("BookNotInDataBase");
            }            
        }

        private static int GetIdOwnership(int idUser, int idBook)
        {
            int result = 0;

            string query = "SELECT idownership FROM vibe.ownership WHERE iduser = @iduser AND idbook = @idbook";

            MySqlCommand cmd = new MySqlCommand(query, connection);

            cmd.Parameters.AddWithValue("@iduser", idUser);
            cmd.Parameters.AddWithValue("@idbook", idBook);

            MySqlDataReader reader = cmd.ExecuteReader();

            while (reader.Read())
            {
                result = reader.GetInt32(0);
            }

            reader.Close();

            return result;
        }

        private static void CancelOwnerShip(int idOwnerShip)
        {

            //create sql command
            string query = "UPDATE vibe.ownership SET date_returned = (SELECT NOW()) WHERE idownership = @idownership";

            MySqlCommand cmd = new MySqlCommand(query, connection);

            cmd.Parameters.AddWithValue("@idownership", idOwnerShip);

            cmd.ExecuteNonQuery();
        }

        private static int GetIdAuthor(string author)
        {
            int result = -1;

            string query = "SELECT idauthor FROM vibe.author WHERE name = @author";

            MySqlCommand cmd = new MySqlCommand(query, connection);

            cmd.Parameters.AddWithValue("@author", author);

            MySqlDataReader reader = cmd.ExecuteReader();

            while (reader.Read())
            {
                result = reader.GetInt32(0);
            }

            reader.Close();

            return result;
        }

        private static int GetIdBook(string isbn, int idUser)
        {
            int result = -1;

            //LIMIT 1 to prevent more than 1 result
            string query = "SELECT idbook FROM vibe.books WHERE isbn = @isbn AND idOwner = @idUser ORDER BY date_borrowed_until LIMIT 1";

            MySqlCommand cmd = new MySqlCommand(query, connection);

            cmd.Parameters.AddWithValue("@isbn", isbn);
            cmd.Parameters.AddWithValue("@idUser", idUser);

            MySqlDataReader reader = cmd.ExecuteReader();

            while (reader.Read())
            {
                result = reader.GetInt32(0);
            }

            reader.Close();

            return result;
        }

        /// <summary>
        /// Tries to find a idbook for a given isbn (-1 if isbn not known or no availiable copies)
        /// </summary>
        /// <param name="isbn"></param>
        /// <returns></returns>
        private static int GetIdBook(string isbn)
        {
            int result = -1;

            //LIMIT 1 to prevent more than 1 result
            string query = "SELECT idbook FROM vibe.books WHERE isbn = @isbn AND idOwner IS NULL";

            MySqlCommand cmd = new MySqlCommand(query, connection);

            cmd.Parameters.AddWithValue("@isbn", isbn);

            MySqlDataReader reader = cmd.ExecuteReader();

            while (reader.Read())
            {
                result = reader.GetInt32(0);
            }

            reader.Close();

            return result;
        }

        private static int GetIdBook(Book book, int idAuthor)
        {
            int result = -1;

            string query = "SELECT idbook FROM vibe.book WHERE title = @title AND idauthor = @idauthor AND year = @year AND isbn = @isbn";

            MySqlCommand cmd = new MySqlCommand(query, connection);

            cmd.Parameters.AddWithValue("@title", book.Title);
            cmd.Parameters.AddWithValue("@idauthor", idAuthor);
            cmd.Parameters.AddWithValue("@year", book.Year);
            cmd.Parameters.AddWithValue("@isbn", book.Isbn);

            MySqlDataReader reader = cmd.ExecuteReader();

            while (reader.Read())
            {
                result = reader.GetInt32(0);
            }

            reader.Close();

            return result;
        }

        public static void AddBook(Book book, bool additionalCopy)
        {
            //does the author already exist?
            int idAuthor = GetIdAuthor(book.Author);

            if (idAuthor < 0)
            {
                AddAuthor(book.Author);
                idAuthor = GetIdAuthor(book.Author);
            }

            //create sql command
            string query = "INSERT INTO vibe.book (title, idauthor, year, isbn)" +
                            "VALUES (@title,@idauthor,@year,@isbn)";

            MySqlCommand cmd = new MySqlCommand(query, connection);

            cmd.Parameters.AddWithValue("@title", book.Title);
            cmd.Parameters.AddWithValue("@idauthor", idAuthor);
            cmd.Parameters.AddWithValue("@year", book.Year);
            cmd.Parameters.AddWithValue("@isbn", book.Isbn);


            //is the book already in the database?

            if (GetIdBook(book, idAuthor) > 0 && !additionalCopy)
            {
                throw new Exception("BookAlreadyInDataBase");
            }
            else
            {
                cmd.ExecuteNonQuery();
            }            
        }

        public static void DeleteBook(int idBook)
        {  
            //create sql command
            string query = "DELETE FROM vibe.ownership where idbook = @idbook;" + "DELETE FROM vibe.book where idbook = @idbook";

            MySqlCommand cmd = new MySqlCommand(query, connection);

            cmd.Parameters.AddWithValue("@idbook", idBook);

            cmd.ExecuteNonQuery();        
        }

        private static void AddAuthor(string name)
        {
            //create sql command
            string query = "INSERT INTO vibe.author(name) VALUES (@name)";

            MySqlCommand cmd = new MySqlCommand(query, connection);

            cmd.Parameters.AddWithValue("@name", name);

            cmd.ExecuteNonQuery();
        }

        public static bool IsUserIdValid(int userId)
        {
            bool result = false;                       

            string query = "SELECT count(*) FROM vibe.user WHERE iduser = @iduser";

            MySqlCommand cmd = new MySqlCommand(query, connection);

            cmd.Parameters.AddWithValue("@iduser", userId);

            MySqlDataReader reader = cmd.ExecuteReader();

            int temp = -1;

            while (reader.Read())
            {
                temp = reader.GetInt32(0);
            }

            reader.Close();

            if (temp > 0)
            {
                result = true;
            }                

            return result;
        }

        public static void DeleteUser(int idUser)
        {         
            //create sql command
            string query = "DELETE FROM vibe.user where iduser= @iduser;" + "UPDATE vibe.ownership SET iduser = null WHERE iduser = @iduser ;";

            MySqlCommand cmd = new MySqlCommand(query, connection);

            cmd.Parameters.AddWithValue("@iduser", idUser);

            cmd.ExecuteNonQuery();            
        }

        public static bool HasActiveOwnerships(int idUser)
        {
            bool result = false;
         
            string query = "SELECT count(*) FROM vibe.ownership WHERE iduser = @iduser AND date_returned IS NULL";

            MySqlCommand cmd = new MySqlCommand(query, connection);

            cmd.Parameters.AddWithValue("@iduser", idUser);

            MySqlDataReader reader = cmd.ExecuteReader();

            int temp = -1;

            while (reader.Read())
            {
                temp = reader.GetInt32(0);
            }

            reader.Close();

            if (temp > 0)
            {
                result = true;
            }
            return result;
        }

        public static void EditUser(User user, int oldIdUser)
        {
          
            string query = String.Empty;

            MySqlCommand cmd = new MySqlCommand();
            cmd.Connection = connection;
            if (IsUserIdValid(oldIdUser))
            {
                //userid changed
                if (user.IdUser != oldIdUser)
                {
                    //is the new user id already in use?
                    if (IsUserIdValid(user.IdUser))
                    {
                        throw new Exception("UserIdAlreadyInUse");
                    }
                    query = "UPDATE vibe.user SET iduser = @iduser, firstname = @firstname,lastname = @lastname, email = @email WHERE iduser = @oldIdUser;";
                    cmd.Parameters.AddWithValue("@oldIdUser", oldIdUser);
                }
                else
                {
                    query = "UPDATE vibe.user SET firstname = @firstname,lastname = @lastname, email = @email WHERE iduser = @iduser;";
                }
            }
            else //new user
            {
                query = "INSERT INTO vibe.user (iduser, firstname, lastname, email) VALUES (@iduser, @firstname, @lastname, @email)";
            }

            //create mysql command
            cmd.CommandText = query;
            cmd.Parameters.AddWithValue("@iduser", user.IdUser);
            cmd.Parameters.AddWithValue("@firstname", user.Firstname);
            cmd.Parameters.AddWithValue("@lastname", user.LastName);
            cmd.Parameters.AddWithValue("@email", user.Email);

            cmd.ExecuteNonQuery();
            
        }

        public static DataTable GetBooks()
        {
            DataTable result = new DataTable();

            string query = "SELECT * FROM vibe.books";

            MySqlCommand cmd = new MySqlCommand(query, connection);

            MySqlDataAdapter dataAdapter = new MySqlDataAdapter(cmd);
            dataAdapter.Fill(result);            

            return result;
        }

        public static DataTable GetUsers()
        {
            DataTable result = new DataTable();         

            string query = "SELECT * FROM vibe.user";

            MySqlCommand cmd = new MySqlCommand(query, connection);

            MySqlDataAdapter dataAdapter = new MySqlDataAdapter(cmd);
            dataAdapter.Fill(result);          

            return result;
        }

        public static double GetAverageBorrowTime(int idBook)
        {
            double result = 0.0;           

            string query = "SELECT COALESCE(avg(datediff(date_returned, date_created)), 0) from ownership where idbook = @idBook AND date_returned IS NOT NULL";

            MySqlCommand cmd = new MySqlCommand(query, connection);

            cmd.Parameters.AddWithValue("@idBook", idBook);

            MySqlDataReader reader = cmd.ExecuteReader();

            while (reader.Read())
            {
                result = reader.GetDouble(0);
            }
            reader.Close();          

            return result;
        }

        public static int GetTimesBorrowed(int idBook, int timeSpanDays)
        {
            int result = 0;

            string query = "SELECT Count(*) FROM vibe.ownership WHERE idbook = @idBook AND date_created > (NOW() - INTERVAL @timespan DAY)";

            MySqlCommand cmd = new MySqlCommand(query, connection);

            cmd.Parameters.AddWithValue("@idBook", idBook);
            cmd.Parameters.AddWithValue("@timespan", timeSpanDays);

            MySqlDataReader reader = cmd.ExecuteReader();

            while (reader.Read())
            {
                result = reader.GetInt32(0);
            }
            reader.Close();

            return result;
        }

        public static void OpenConnection()
        {
            if (!(connection.State == ConnectionState.Open))
            {
                connection.Open();
            }
        }

        public static void CloseConnection()
        {
            if (!(connection.State == ConnectionState.Closed))
            {
                connection.Close();
            }
        }

        public static List<MailReminder> GetMailReminders()
        {
            List<MailReminder> result = new List<MailReminder>();

            string query =  "SELECT o.idownership, u.email, b.title, o.date_borrowed_until " +
                            "FROM vibe.ownership o " +
                            "JOIN vibe.user u on o.iduser = u.iduser " +
                            "JOIN vibe.book b on o.idbook = b.idbook "+ 
                            "WHERE NOW() > date_borrowed_until "+
                            "AND date_mail_sent IS NULL OR (TIMESTAMPDIFF(DAY,date_mail_sent, NOW())) > 7";


            MySqlCommand cmd = new MySqlCommand(query, connection);

            MySqlDataReader reader = cmd.ExecuteReader();

            while (reader.Read())
            {
                MailReminder reminder = new MailReminder();

                reminder.Email = reader.GetString("email");
                reminder.Title = reader.GetString("title");
                reminder.ReturnUntil = reader.GetDateTime("date_borrowed_until");
                reminder.idOwnership = reader.GetInt32("idownership");

                result.Add(reminder);
            }
            reader.Close();  

            return result;
        }

        public static List<string> GetAdminEmails()
        {
            List<string> result = new List<string>();

            string query = "SELECT email from vibe.user WHERE admin = 1";


            MySqlCommand cmd = new MySqlCommand(query, connection);

            MySqlDataReader reader = cmd.ExecuteReader();

            while (reader.Read())
            {
                result.Add(reader.GetString("email"));
            }
            reader.Close();  

            return result;
        }

        public static void UpdateMailSent(int idOwnership)
        {
            //create sql command
            string query = "UPDATE vibe.ownership SET date_mail_sent = NOW() WHERE idownership = @idownership";

            MySqlCommand cmd = new MySqlCommand(query, connection);

            cmd.Parameters.AddWithValue("@idownership", idOwnership);

            cmd.ExecuteNonQuery(); 
        }

        public static bool LoginAdmin(int idUser, string password)
        {
            bool result = false;

            string query = "SELECT * FROM vibe.user WHERE idUser = @idUser AND password = @password AND admin = 1";


            MySqlCommand cmd = new MySqlCommand(query, connection);

            cmd.Parameters.AddWithValue("@idUser", idUser);
            cmd.Parameters.AddWithValue("@password", password);

            MySqlDataReader reader = cmd.ExecuteReader();

            while (reader.Read())
            {
                result = true;
            }

            reader.Close();

            return result;
        }
    }
}
